#  -*- coding: utf-8 -*-

from pymongo import UpdateOne
from datetime import datetime
import time,json
import pandas as pd
import os,ast

from pymongo import MongoClient, ASCENDING, DESCENDING, UpdateOne

from HunterHouse.database import DB_CONN

class DataAnalyze:
    def __init__(self):
        self.sell_house = DB_CONN['sell_house']
        self.rent_house = DB_CONN['rent_house']
        self.community = DB_CONN['community']

    def analyze_rent_sell_ratio(self):
        #获得小区列表
        rent_sell_ratio_list = list()
        community_cursor = self.community.find(
            {'month': "3"})

        for community_info in community_cursor:
            community = community_info['name']
            section = community_info['section']
            street = community_info["street"]
            follow_num = community_info['follow_num']
            building_company = ""
            building_time = ""
            house_num = ""
            manage_company = ""
            if 'building_time' in community_info.keys():
                building_time = community_info['building_time']
            if 'building_company' in community_info.keys():
                building_company = community_info['building_company']
            if 'house_num' in community_info.keys():
                house_num = community_info['house_num']
            if 'manage_company' in community_info.keys():
                manage_company = community_info['manage_company']



            total_price = 0
            total_area = 0
            total_unit_price = 0

            community_dict = dict()

            # 通过小区名称，查到该小区所有租房信息，计算单位面积租金平均价格
            rent_cursor = self.rent_house.find(
                {'title': {'$regex':community}},
                projection={'price': True, 'house_area':True, '_id': False})
            for rent in rent_cursor:
                total_price += rent['price']
                total_area += rent['house_area']

            if total_area != 0:
                avg_rent_area = round(total_area/rent_cursor.count(),2)
                avg_rent_price = round(total_price / total_area,2)


            # 通过小区名称，查到该小区所有在售房屋信息，计算单位面积平均价格
            sell_cursor = self.sell_house.find(
                {'communityname': {'$regex':community}},
                projection={'unitprice': True, '_id': False})

            for sell in sell_cursor:
                total_unit_price += sell['unitprice']

            if sell_cursor.count() != 0:
                avg_sell_price = round(total_unit_price / sell_cursor.count(),2)

            #计算租售比（年化）
            community_dict['name'] = community
            community_dict['section'] = section
            community_dict['street'] = street
            community_dict['follow_num'] = follow_num
            community_dict['building_company'] = building_company
            community_dict['building_time'] = building_time
            community_dict['house_num'] = house_num
            community_dict['manage_company'] = manage_company
            community_dict['sell_count'] = sell_cursor.count()
            community_dict['rent_count'] = rent_cursor.count()
            community_dict["rent_avg_area"] =  avg_rent_area
            community_dict['avg_rent_price'] = avg_rent_price
            community_dict['avg_sell_price'] = avg_sell_price

            ratio = round(100*avg_rent_price*12/avg_sell_price,2)
            community_dict['ratio'] = ratio
            rent_sell_ratio_list.append(community_dict)
            print(community_dict)
        #rent_sell_ratio_list = sorted(rent_sell_ratio_list, key=lambda item:item[0]['ratio'])
        rent_sell_ratio_df = pd.DataFrame(rent_sell_ratio_list)
        rent_sell_ratio_df.to_csv("rent_sell_ratio.csv")

if __name__ == '__main__':
    data_analyze = DataAnalyze()
    data_analyze.analyze_rent_sell_ratio()